package com.thekernel.youdaoyun.mapper;

import com.thekernel.youdaoyun.bean.Note;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * @Author TheKernel
 * @Date 2019/9/14 11:47 上午
 * @Version 1.0
 * @Description 操作 note 数据库
 */
@Component
@Mapper
public interface NoteMapper {

    /**
     * 添加笔记
     *
     * @param note 笔记信息
     * @return 0: 添加失败 1: 添加成功
     */
    @Insert("INSERT INTO `note`(`note_title`, `category`, `note_content`, `type`, `note_user`, `status`, " +
            "`create_time`) VALUES(#{noteTitle}, #{category}, #{noteContent}, #{type}, #{noteUser}, #{status}, " +
            "#{createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "noteId")
    int add(Note note);

    /**
     * 查询所有个人有效笔记
     *
     * @param userId 指定用户id
     * @return 有效笔记信息集合
     */
    @Select("SELECT * FROM `note` WHERE `status`=1 AND `note_user`=#{userId}")
    @Results(
            id = "validNotes",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "folder", column = "folder", javaType = Boolean.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class),
                    @Result(property = "updateTime", column = "update_time", javaType = Date.class),
            }
    )
    Note[] queryAllValidNotesByUserId(Long userId);

    /**
     * 查询回收站中的笔记
     *
     * @param userId 指定用户id
     * @return 回收站的笔记信息集合
     */
    @Select("SELECT * FROM `note` WHERE `status`=0 AND `note_user`=#{userId}")
    @Results(
            id = "deletedNotes",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "folder", column = "folder", javaType = Boolean.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class)
            }
    )
    Note[] queryAllDeletedNotesByUserId(Long userId);

    /**
     * 逻辑删除笔记
     *
     * @param noteId 笔记 id
     * @return 1: 删除成功 0: 删除失败
     */
    @Update("UPDATE `note` SET `status`=0 WHERE `note_id`=#{noteId} LIMIT 1")
    int deleteNoteByNoteId(long noteId);

    /**
     * 通过 id 彻底删除笔记
     *
     * @param noteId 笔记 id
     * @return 1: 删除成功 0: 删除失败
     */
    @Delete("DELETE FROM `note` WHERE `note_id`=#{noteId} LIMIT 1")
    int deleteDeletedNoteByNoteId(long noteId);

    /**
     * 恢复删除笔记
     *
     * @param noteId 笔记 id
     * @return 1: 恢复成功 0: 恢复失败
     */
    @Update("UPDATE `note` SET `status`=1 WHERE `note_id`=#{noteId} LIMIT 1")
    int recoverDeletedNotes(long noteId);

    /**
     * 用于判断该笔记是否属于该用户
     *
     * @param noteId 笔记id
     * @return 笔记信息(笔记拥有者)
     */
    @Select("SELECT `note_user` FROM `note` WHERE `note_id`=#{noteId} LIMIT 1")
    @Results(
            id = "noteUser",
            value = {
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class)
            }
    )
    Note queryNoteUserByNoteId(long noteId);

    /**
     * 获取指定 Note
     *
     * @param noteId 笔记id
     * @return 笔记信息
     */
    @Select("SELECT * FROM `note` WHERE `note_id`=#{noteId} LIMIT 1")
    @Results(
            id = "note",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class)
            }
    )
    Note queryNoteByNoteId(long noteId);

    /**
     * 用于检测笔记是否存在
     *
     * @param noteId 笔记id
     * @return 查询到的笔记
     */
    @Select("SELECT * FROM `note` WHERE `note_id`=#{noteId} AND `status`=1 LIMIT 1")
    @Results(
            id = "validNote",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class)
            }
    )
    Note queryValidNoteByNoteId(long noteId);

    /**
     * 用于检测已删除的笔记是否存在
     *
     * @param noteId 笔记id
     * @return 已删除的笔记
     */
    @Select("SELECT `note_user` FROM `note` WHERE `note_id`=#{noteId} AND `status`=0 LIMIT 1")
    @Results(
            id = "deletedNote",
            value = {
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class)
            }
    )
    Note queryDeletedNoteByNoteId(long noteId);

    /**
     * 更新笔记
     *
     * @param note 更新后的笔记内容
     * @return 1: 更新成功 0: 更新失败
     */
    @Update("UPDATE `note` SET `note_secret`=#{noteSecret}, `category`=#{category} " +
            "WHERE `note_id`=#{noteId} AND `status`=1")
    int updateNote(Note note);

    /**
     * 重命名笔记
     *
     * @param note Note(note_id, note_title) 更新后的笔记名
     * @return 1: 重命名成功 0: 重命名失败
     */
    @Update("UPDATE `note` SET `note_title`=#{noteTitle} WHERE `note_id`=#{noteId} AND `status`=1")
    int renameNote(Note note);

    /**
     * 更新笔记内容
     *
     * @param note 笔记内容
     * @return true: 更新成功 false: 更新失败
     */
    @Update("UPDATE `note` SET `note_title`=#{noteTitle}, `create_time`=#{createTime} " +
            "WHERE `note_id`=#{noteId} AND `status`=1")
    boolean updateNoteContent(Note note);

    /**
     * 添加文件夹
     *
     * @param note 文件夹信息
     * @return 文件夹id
     */
    @Insert("INSERT INTO `note`(`note_title`, `folder`, `category`, `note_user`, `status`, `create_time`) " +
            "VALUES(#{noteTitle}, true, #{category}, #{noteUser}, true, #{createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "noteId")
    int addFolder(Note note);

    @Select("SELECT `note_id` FROM `note` WHERE `category`=#{noteId} AND `folder`=0 AND `status`=1")
    @Results(
            id = "belongFolder",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class)
            }
    )
    Note[] queryNotesBelongFolder(long noteId);

    @Select("SELECT `note_id` FROM `note` WHERE `category`=#{noteId} AND `folder`=0 AND `status`=0")
    @Results(
            id = "deletedBelongFolder",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class)
            }
    )
    Note[] queryDeletedNotesBelongFolder(long noteId);

    @Delete("DELETE FROM `note` WHERE `note_user`=#{userId} AND `status`=0")
    int deletedAllDeleted(long userId);

    @Update("UPDATE `note` SET `status`=1 WHERE `note_user`=#{userId} AND `status`=0")
    int recoverAllDeleted(long userId);

    @Select("SELECT * FROM `note` WHERE `note_title` LIKE '%${keyword}%' AND `status`=1 AND `note_user`=#{userId}")
    @Results(
            id = "keyword",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class)
            }
    )
    Note[] queryNotesByKeyword(String keyword, long userId);

    @Select("SELECT * FROM `note` WHERE `category`=#{category} AND `status`=1 AND `note_user`=#{userId}")
    @Results(
            id = "notesUnderFolder",
            value = {
                    @Result(property = "noteId", column = "note_id", javaType = Long.class),
                    @Result(property = "noteTitle", column = "note_title", javaType = String.class),
                    @Result(property = "noteContent", column = "note_content", javaType = String.class),
                    @Result(property = "noteUser", column = "note_user", javaType = Long.class),
                    @Result(property = "category", column = "category", javaType = Integer.class),
                    @Result(property = "status", column = "status", javaType = Boolean.class),
                    @Result(property = "createTime", column = "create_time", javaType = Date.class)
            }
    )
    Note[] queryNotesByCategory(long category, long userId);

    @Update("UPDATE `note` SET `z`=#{z} WHERE `note_id`=#{noteId} AND `status`=1")
    int addZ(long noteId, int z);

    @Select("SELECT MAX(`z`) FROM `note`")
    int maxZ();

}
